REM  SQL scripts for Chapter 12 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.



create tablespace CODES_TABLES
datafile '/u01/oracle/VLDB/codes_tables.dbf'
default storage
  (initial 1M next 1M pctincrease 0 pctfree 2);


create table EMPLOYEE (
EmpNo          NUMBER(10) primary key,
Name           VARCHAR2(40),
DeptNo         NUMBER(2),
Salary         NUMBER(7,2),
Birth_Date     DATE,
Soc_Sec_Num    VARCHAR2(9),
State_Code     CHAR(2),
constraint FK_DeptNO foreign key (DeptNo)
   references DEPT(DeptNo),
constraint FK_StateCode foreign key (State_Code)
   references State(State_Code),
);


create table EMPLOYEE (
EmpNo          NUMBER(10) primary key,
Name           VARCHAR2(40),
DeptNo         NUMBER(2),
Salary         NUMBER(7,2),
Birth_Date     DATE,
Soc_Sec_Num    VARCHAR2(9),
 constraint FK_DeptNO foreign key (DeptNo)
   references DEPT(DeptNo)
)
partition by range (DeptNo)
 (partition PART1   values less than (11)
   tablespace PART1_TS,
  partition PART2   values less than (21)
   tablespace PART2_TS,
  partition PART3   values less than (31)
   tablespace PART3_TS,
  partition PART4   values less than (MAXVALUE)
   tablespace PART4_TS)
;


select *
  from EMPLOYEE (PART2)
 where DeptNo between 11 and 20;


create index EMPLOYEE_DEPTNO
  on EMPLOYEE(DeptNo)
   local
   (partition PART1
     tablespace PART1_NDX_TS,
    partition PART2
     tablespace PART2_NDX_TS,
    partition PART3
     tablespace PART3_NDX_TS,
    partition PART4
     tablespace PART4_NDX_TS)


create index EMPLOYEE_DEPTNO
on EMPLOYEE(DeptNo)
 global partition by range (DeptNo)
 (partition PART1   values less than (11)
   tablespace PART1_NDX_TS,
  partition PART2   values less than (21)
   tablespace PART2_NDX_TS,
  partition PART3   values less than (31)
   tablespace PART3_NDX_TS,
  partition PART4   values less than (MAXVALUE)
   tablespace PART4_NDX_TS)
;


alter table [user.]TABLE
  | modify partition PARTITION_NAME
             { storage
             | [logging | nologging] } ...
  | move partition PARTITION_NAME 
             { physical_attributes_clause
             | [logging | nologging]
             | tablespace tablespace
             | parallel parallel_clause} ...
  | add partition [NEW_PARTITION_NAME]
        values less than (value_list)
             { physical_attributes_clause
             | [logging | nologging]
             | tablespace tablespace } ...
  | drop partition PARTITION_NAME
  | truncate partition PARTITION_NAME
             [drop storage | reuse storage]
  | split partition PARTITION_NAME_OLD
              at (value_list)
    [into ( partition [SPLIT_PARTITION_1]
              [storage
              |  [logging | nologging]
              |  tablespace tablespace ] ...
          , partition [SPLIT_PARTITION 2]
              [storage
              |  [logging | nologging]
              |  tablespace tablespace ] ...) ]
              [ parallel parallel_clause ] ...
  | exchange partition PARTITION_NAME
       with table NON_PARTITIONED_TABLE_NAME
        [{including | excluding} indexes]
        [{with | without} validation ]
  | modify partition unusable local indexes
  | modify partition rebuild unusable local indexes }


alter table EMPLOYEE
   modify partition PART1
   storage (next 1M pctincrease 0);


alter table EMPLOYEE
  truncate partition PART3
  drop storage;


alter index [user.]INDEX
 | modify partition PARTITION_NAME
             [ storage
             | {logging | nologging}
             | unusable ]
| rename partition PARTITION_NAME
             to NEW_PARTITION_NAME
| drop partition PARTITION_NAME
| split partition PARTITION_NAME_OLD
             at (value_list)
   [ into ( partition [SPLIT_PARTITION_1]
             [ storage
             | tablespace tablespace
             | {logging | nologging} ... ]
            partition [SPLIT_PARTITION_2]
             [ storage
             | tablespace tablespace
             | {logging | nologging} ... ] ) ]
   [ parallel parallel_clause | noparallel ]
| rebuild partition PARTITION_NAME 
             [ storage
             | tablespace tablespace
             | {parallel parallel_clause | noparallel}
             | {logging | nologging} ... ]
| unusable


alter index EMPLOYEE_DEPTNO
rebuild partition PART4
storage (initial 2M next 2M pctincrease 0);


create index STATE_CODE_DESCRIPTION
on STATE(State_Code, Description);

create index STATE_DESCRIPTION_CODE
on STATE(Description, State_Code);


create table STATE (
State_Code      CHAR(2) primary key,
Description     VARCHAR2(25)
)
organization index;


create bitmap index EMPLOYEE$STATE_CODE$BMAP
    on EMPLOYEE(State_Code);



set transaction use rollback segment SEGMENT_NAME;


insert /*+ APPEND */ into SALES_PERIOD_CUST_AGG
select Period_ID, Customer_ID, SUM(Sales)
  from SALES
 group by Period_ID, Customer_ID;


delete from SALES where Customer_ID=12;

execute DELETE_COMMIT('delete from SALES where Customer_ID=12',1000);
execute DELETE_COMMIT('delete from SALES where State_Code = ''NH''',500)

create or replace procedure DELETE_COMMIT
( p_statement in varchar2,
  p_commit_batch_size   in number default 10000)
is
        cid                             integer;
        changed_statement               varchar2(2000);
        finished                        boolean;
        nofrows                         integer;
        lrowid                          rowid;
        rowcnt                          integer;
        errpsn                          integer;
        sqlfcd                          integer;
        errc                            integer;
        errm                            varchar2(2000);
begin
        /* If the actual statement contains a WHERE clause, then append a
           rownum < n clause after that using AND, else use WHERE
     rownum < n clause */
        if ( upper(p_statement) like '% WHERE %') then
                changed_statement := p_statement||' AND rownum < '
               ||to_char(p_commit_batch_size + 1);
        else
changed_statement := p_statement||' WHERE rownum < '
||to_char(p_commit_batch_size + 1);
        end if;
        begin
  cid := dbms_sql.open_cursor; -- Open a cursor for the task
                dbms_sql.parse(cid,changed_statement, dbms_sql.native);
                        -- parse the cursor.
  rowcnt := dbms_sql.last_row_count;
                      -- store for some future reporting
        exception
           when others then
                     errpsn := dbms_sql.last_error_position;
                        -- gives the error position in the changed sql
                        -- delete statement if anything happens
     sqlfcd := dbms_sql.last_sql_function_code;
                        -- function code can be found in the OCI manual
                     lrowid := dbms_sql.last_row_id;
                        -- store all these values for error reporting. However
                        -- all these are really useful in a stand-alone proc
                        -- execution for dbms_output to be successful, not
                        -- possible when called from a form or front-end tool.
                     errc := SQLCODE;
                     errm := SQLERRM;
                     dbms_output.put_line('Error '||to_char(errc)||
                           ' Posn '||to_char(errpsn)||
               ' SQL fCode '||to_char(sqlfcd)||
        ' rowid '||rowidtochar(lrowid));
                    raise_application_error(-20000,errm);
                        -- this will ensure the display of at least the error
                        -- message if something happens, even in a front-end
                        -- tool.
        end;
        finished := FALSE;
        while not (finished)
        loop -- keep on executing the cursor till there is no more to process.
                begin
 nofrows := dbms_sql.execute(cid);
                        rowcnt := dbms_sql.last_row_count;
                exception
                        when others then
                                errpsn := dbms_sql.last_error_position;
                         sqlfcd := dbms_sql.last_sql_function_code;
                    lrowid := dbms_sql.last_row_id;
    errc := SQLCODE;
                                errm := SQLERRM;
                      dbms_output.put_line('Error '||to_char(errc)||
                             ' Posn '||to_char(errpsn)||
                 ' SQL fCode '||to_char(sqlfcd)||
          ' rowid '||rowidtochar(lrowid));
                                raise_application_error(-20000,errm);
                end;
                if nofrows = 0 then
                        finished := TRUE;
                else
                 finished := FALSE;
                end if;
                commit;
        end loop;
        begin
                dbms_sql.close_cursor(cid);
                        -- close the cursor for a clean finish
        exception
                when others then
                        errpsn := dbms_sql.last_error_position;
                        sqlfcd := dbms_sql.last_sql_function_code;
                        lrowid := dbms_sql.last_row_id;
     errc := SQLCODE;
                        errm := SQLERRM;
       dbms_output.put_line('Error '||to_char(errc)||
      ' Posn '||to_char(errpsn)||
                                ' SQL fCode '||to_char(sqlfcd)||
                                ' rowid '||rowidtochar(lrowid));
                        raise_application_error(-20000,errm);
        end;
end;
/



